# _*_ coding:utf-8 _*_
# @File  : user_sheets.py
# @Time  : 2021-11-11  10:00
# @Author: zizle


from fastapi import APIRouter, Query, Body
from pydantic import BaseModel, constr, conint
from db import FAConnection
from status import r_status

usheets_api = APIRouter()


# 获取用户的品种数据表
@usheets_api.get('/', summary='获取品种下的所有表')
async def get_variety_sheets(v: str = Query(..., max_length=2, min_length=1)):
    db = FAConnection(conn_name='admin查询品种数据表')
    sql = 'SELECT a.id,a.creator,a.update_by,a.create_time,a.update_time,a.variety_en,a.sheet_name,a.db_table,' \
          'a.min_date,a.max_date,a.update_count,a.suffix,a.is_private,a.is_dated ' \
          'FROM industry_user_sheet AS a ' \
          'WHERE a.variety_en=%s;'
    records = db.query(sql, param=[v], keep_conn=True)
    # 查询用户
    user_list = db.query('SELECT id,username FROM user_user;')
    user_dict = {u['id']: u['username'] for u in user_list}

    for item in records:
        item['creator'] = user_dict.get(item['creator'], '未知')
        item['update_by'] = user_dict.get(item['update_by'], '未知')
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d')
        item['update_time'] = item['update_time'].strftime('%Y-%m-%d')

    return {'code': r_status.SUCCESS, 'message': '查询数据表成功!', 'data': records}


class SheetBelongItem(BaseModel):
    variety_en: constr(min_length=1, max_length=2)
    belong_id: conint(ge=1)
    contain_chart: conint(ge=0, le=1)


# 修改这个品种所有表的归属人
@usheets_api.put('/all/', summary='修改所有品种表的信息')
async def modify_variety_sheets(belong_item: SheetBelongItem = Body(...)):
    db = FAConnection(conn_name='修改表归属')
    sql = 'UPDATE industry_user_sheet SET creator=%s WHERE variety_en=%s;'
    chart_sql = 'UPDATE industry_user_chart SET creator=%s WHERE variety_en=%s;'

    params = [belong_item.belong_id, belong_item.variety_en]
    if belong_item.contain_chart:
        sql_list = [sql, chart_sql]
        param_list = [params, params]
    else:
        sql_list = [sql]
        param_list = [params]

    success = db.execute_tasks(sql_list=sql_list, param_list=param_list)
    if success:
        return {'code': r_status.SUCCESS, 'message': '修改表归属成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '修改表归属失败,服务器错误!'}

